CREATE FOREIGN TABLE

CREATE FOREIGN TABLE — Define a new foreign table

Synopsis

CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name ( [

{ column_name data_type [ OPTIONS ( option 'value' [, ... ] ) ]

[ COLLATE collation ] [ column_constraint [ ... ] ]

| table_constraint }

[, ... ]

] )

[ INHERITS ( parent_table [, ... ] ) ]

SERVER server_name

[ OPTIONS ( option 'value' [, ... ] ) ]

CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name

PARTITION OF parent_table [ (

{ column_name [ WITH OPTIONS ] [ column_constraint [ ... ] ]

| table_constraint }

[, ... ]

) ] partition_bound_spec

SERVER server_name

[ OPTIONS ( option 'value' [, ... ] ) ]

where column_constraint is:

[ CONSTRAINT constraint_name ]

{ NOT NULL |

NULL |

CHECK ( expression ) [ NO INHERIT ] |

DEFAULT default_expr |

GENERATED ALWAYS AS ( generation_expr ) STORED }

and table_constraint is:

[ CONSTRAINT constraint_name ]

CHECK ( expression ) [ NO INHERIT ]

Description

CREATE FOREIGN TABLE creates a new foreign table in the current database. The table will be owned by the user issuing the command.

If a schema name is given (for example, CREATE FOREIGN TABLE myschema.mytable ...), the table will be created in the specified schema. Otherwise, it will be created in the current schema. The name of the foreign table must be distinct from the name of any other foreign table, table, sequence, index, view, or materialized view in the same schema.

CREATE FOREIGN TABLE also automatically creates a data type that represents the composite type corresponding to the foreign table's row. Therefore, a foreign table cannot have the same name as any existing data type in the same schema.

If the PARTITION OF clause is specified, the table is created as a partition of parent_table with the specified bounds.

To create a foreign table, you must have USAGE privilege on the foreign server, as well as USAGE privilege on all column types used in the table.

Parameters

IF NOT EXISTS

Do not throw an error if a relation with the same name already exists. A notice will be issued in this case. Note that there is no guarantee that the existing relation is anything like the one that would be created.

table_name

The name (optionally schema-qualified) of the table to be created.

column_name

The name of a column to be created in the new table.

data_type

The data type of the column. This can include array specifiers.

COLLATE collation

The COLLATE clause assigns a collation to the column (which must be of a collatable data type). If not specified, the default collation of the column's data type is used.

INHERITS ( parent_table [, ... ] )

The optional INHERITS clause specifies a list of tables from which the new foreign table automatically inherits all columns. Parent tables can be plain tables or foreign tables. See the similar form of CREATE TABLE for details.

PARTITION OF parent_table FOR VALUES partition_bound_spec

This statement can be used to create a foreign table as a partition of a parent table with a specified bound. See the similar form of CREATE TABLE for details. Note that if the parent table has a UNIQUE index, it is currently not allowed to create a foreign table as a partition of that parent table. (See also ALTER TABLE ATTACH PARTITION.)

CONSTRAINT constraint_name

An optional name for a column or table constraint. If the constraint is violated, the constraint name will appear in error messages, so constraint names like col must be positive can be used to communicate useful constraint information to client applications (to specify a constraint name containing spaces, use double quotes). If no constraint name is specified, the system will generate one automatically.

NOT NULL

The column does not allow null values.

NULL

The column can contain null values. This is the default.

This clause is provided only for compatibility with non-standard SQL databases. Its use is discouraged in new applications.

CHECK ( expression ) [ NO INHERIT ]

The CHECK clause specifies an expression that produces a Boolean result, which every row in the foreign table should satisfy. That is, for all rows in the foreign table, the expression should produce TRUE or UNKNOWN, but not FALSE. A check constraint specified as a column constraint should only reference the column's value, while an expression appearing in a table constraint can reference multiple columns.

Currently, CHECK expressions cannot contain subqueries and cannot reference variables other than columns of the current row. The system column tableoid can be referenced, but no other system columns.

A constraint marked with NO INHERIT will not propagate to child tables.

DEFAULT default_expr

The DEFAULT clause assigns a default data value for the column. The value is any variable-free expression (subqueries and cross-references to other columns in the current table are not allowed). The data type of the default expression must match the column's data type.

The default expression will be used in any insert operation that does not specify a column value. If a column has no default value, the default is the null value.

GENERATED ALWAYS AS ( generation_expr ) STORED

This clause creates a generated column. This column cannot be written to, and will only return the value of the specified expression when read.

The keyword STORED is required to indicate that the column will be computed on write. (The computed value will be passed to the foreign data wrapper for storage, and returned on read.)

The generation expression can reference other columns in the table, but cannot reference other generated columns. The functions and operators used must be immutable. References to other tables are not allowed.

server_name

The name of an existing foreign server to use for the foreign table. For details on defining a server, see CREATE SERVER.

OPTIONS ( option 'value' [, ...] )

Options to be associated with the new foreign table or one of its columns. The allowed option names and values are specific to each foreign-data wrapper, and they are validated by the foreign-data wrapper's validator function. Duplicate option names are not allowed (however, a table option and a column option can have the same name).

Notes

The system does not enforce constraints (such as CHECK or NOT NULL clauses) on foreign tables, and most foreign-data wrappers do not attempt to enforce them either. That is, such constraints are simply assumed to hold true. This enforcement is somewhat meaningless, because it only applies to rows inserted or updated through the foreign table, and has no effect on rows modified through other means (such as direct modifications on the remote server). Constraints attached to a foreign table should represent constraints enforced by the foreign server. Some special-purpose foreign-data wrappers may be the only access mechanism for the data they access, in which case it may be appropriate for the foreign-data wrapper itself to perform constraint enforcement. However, you should not assume that a wrapper does this unless its documentation says so.

Although the system attempts to enforce constraints on foreign tables, it does assume that they are correct for query optimization purposes. If there are rows visible in the foreign table that do not satisfy the constraints, queries on the table may produce incorrect results. It is the user's responsibility to ensure that the constraint definitions match reality.

Similar considerations apply to generated columns. Generated columns are computed on the local server during inserts or updates, and the computed results are passed to the foreign data wrapper, which is responsible for writing the results to the external data store. However, there is no guarantee that the generated column values returned when querying the foreign table are consistent with the generation expression. In summary, this behavior may lead to incorrect query results.

Although columns can be moved from a local partition to a foreign table partition (using a foreign data wrapper that supports tuple routing), columns cannot be moved from a foreign table partition to a local partition.

Examples

Create a foreign table films, accessed through the server film_server:

CREATE FOREIGN TABLE films (

code char(5) NOT NULL,

title varchar(40) NOT NULL,

did integer NOT NULL,

date_prod date,

kind varchar(10),

len interval hour to minute

)

SERVER film_server;

Create a foreign table measurement_y2016m07, accessed through server server_07, as a partition of the range-partitioned table measurement:

CREATE FOREIGN TABLE measurement_y2016m07

PARTITION OF measurement FOR VALUES FROM ('2016-07-01') TO ('2016-08-01')

SERVER server_07;

See Also

ALTER FOREIGN TABLE, DROP FOREIGN TABLE, CREATE TABLE, CREATE SERVER, IMPORT FOREIGN SCHEMA